﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Genealogy_Builder
{
    class Query
    {
        public static string GenerationsBackViews(int n, int root)
        {
            if (n < 1)
                throw new Exception("0 generations cannot be selected");

            StringBuilder cmdString = new StringBuilder("CREATE OR REPLACE VIEW Gen"+n.ToString()+" AS (SELECT R"+(n-1)+".* FROM ");
            for (int i = 0; i < n; i++)
            {
                if (i != 0)
                {
                    cmdString.Append(", ");
                }
                cmdString.Append("Relatives AS R" + i.ToString());
            }
            cmdString.Append(" WHERE R0.person_b = " + root.ToString() + " AND R0.type = 'Parent-Child'");
            for (int i = 1; i < n; i++)
            {
                cmdString.Append(" AND R"+i.ToString()+".person_b = R"+(i-1).ToString()+".person_a AND R"+i.ToString()+".type = 'Parent-Child'");
            }

            cmdString.Append(");");

            return cmdString.ToString();
        }

        public static string GenerationsForwardViews(int n)
        {
            StringBuilder cmdString = new StringBuilder();

            for (int gen = (n - 1); gen >= 0; gen--)
            {
                //union the last generation with spouses
             //   cmdString.Append("CREATE OR REPLACE VIEW UGen" + (gen + 1).ToString() + " AS (SELECT R1.person_a, R1.person_b FROM Gen" + (gen+1).ToString() + " AS R0, Relatives AS R1 WHERE (R0.person_b = R1.person_a OR R0.person_b = R1.person_b) AND R1.type = 'Marriage') UNION DISTINCT (SELECT R.person_b AS person_a, R.person_b AS person_b FROM Gen" + (gen+1).ToString() + " AS R);");
                //create next gen with children
                cmdString.Append("CREATE OR REPLACE VIEW Gen" + gen.ToString() + " AS (SELECT R1.* FROM Gen" + (gen + 1).ToString() + " AS R0, Relatives AS R1 WHERE ");
                if(gen == (n-1)){
                    cmdString.Append("(R0.person_a = R1.person_a)");
                }
                else{
                    cmdString.Append("(R0.person_b = R1.person_a)");
                }
                cmdString.Append(" AND R1.type = 'Parent-Child');");
            }

            return cmdString.ToString();
        }

        public static string GenerationsForwardViews(int n, int root)
        {
            if (n < 1)
                throw new Exception("0 generations cannot be selected");

            StringBuilder cmdString = new StringBuilder("CREATE OR REPLACE VIEW SelectedFamily AS SELECT R" + (n - 1) + ".* FROM ");
            for (int i = 0; i < n; i++)
            {
                if (i != 0)
                {
                    cmdString.Append(", ");
                }
                cmdString.Append("Relatives AS R" + i.ToString());
            }
            cmdString.Append(" WHERE R0.person_a = " + root.ToString() + " AND R0.type = 'Parent-Child'");
            for (int i = 1; i < n; i++)
            {
                cmdString.Append(" AND R" + i.ToString() + ".person_a = R" + (i - 1).ToString() + ".person_b AND R" + i.ToString() + ".type = 'Parent-Child'");
            }
            cmdString.Append("; CREATE OR REPLACE VIEW SelectedFamily AS SELECT person_a FROM Gen0 UNION DISTINCT SELECT person_b FROM Gen0");
            for (int i = 1; i <= n; i++)
            {
                cmdString.Append(" UNION DISTINCT SELECT person_a FROM Gen" + i.ToString() + " UNION DISTINCT SELECT person_b FROM Gen" + i.ToString());
            }
            cmdString.Append("; CREATE OR REPLACE VIEW SelectedSpouses AS SELECT R.person_a, R.person_b FROM SelectedFamily AS S, Relatives AS R WHERE (S.person_a = R.person_a OR S.person_a = R.person_b) AND R.type = 'Marriage'");
            cmdString.Append("; SELECT person_a FROM SelectedFamily UNION DISTINCT SELECT person_a FROM SelectedSpouses UNION DISTINCT SELECT person_b FROM SelectedSpouses");
            
            return cmdString.ToString();
        }


        public static string BackwardGenerations(int n, int root)
        {
            StringBuilder cmdString = new StringBuilder();
            cmdString.Append(GenerationsBackViews(n, root));
            cmdString.Append(GenerationsForwardViews(n));
            cmdString.Append("CREATE OR REPLACE VIEW SelectedFamily AS SELECT person_a FROM Gen0 UNION DISTINCT SELECT person_b FROM Gen0");
            for (int i = 1; i <= n; i++)
            {
                cmdString.Append(" UNION DISTINCT SELECT person_a FROM Gen" + i.ToString() + " UNION DISTINCT SELECT person_b FROM Gen" + i.ToString());
            }
            cmdString.Append("; CREATE OR REPLACE VIEW SelectedSpouses AS SELECT R.person_a, R.person_b FROM SelectedFamily AS S, Relatives AS R WHERE (S.person_a = R.person_a OR S.person_a = R.person_b) AND R.type = 'Marriage'");
            cmdString.Append("; SELECT person_a FROM SelectedFamily UNION DISTINCT SELECT person_a FROM SelectedSpouses UNION DISTINCT SELECT person_b FROM SelectedSpouses");
                        
            return cmdString.ToString();
        }

        public static string GetCreator()
        {
            return "CREATE TABLE Places(" +
            "    `place_id` int(11) NOT NULL AUTO_INCREMENT," +
	        "    `zip_code` varchar(10) NOT NULL," +
	        "    `city` varchar(255) NOT NULL," +
	        "    `state` varchar(255)," +
	        "    `country` varchar(255) NOT NULL," +
	        "    PRIMARY KEY (`place_id`)," +
	        "   INDEX(`state`)," +
	        "   INDEX(`country`)" +
            ") ENGINE=InnoDB;" +

            "CREATE TABLE Person(" +
	        "    `person_id` int(11) NOT NULL AUTO_INCREMENT," +
	        "    `name` varchar(255) NOT NULL," +
	        "    `date_of_birth` date NOT NULL," +
	        "    `date_of_death` date," +
	        "    `from` int(11)," +
	        "    `lives_in` int(11)," +
	        "    PRIMARY KEY (`person_id`)," +
	        "    FOREIGN KEY (`from`) REFERENCES Places(`place_id`) ON UPDATE CASCADE ON DELETE SET NULL," +
	        "    FOREIGN KEY (`lives_in`) REFERENCES Places(`place_id`) ON UPDATE CASCADE ON DELETE SET NULL," +
            "   INDEX(`date_of_birth`)," + 
	        "   INDEX(`date_of_death`)," +
	        "   INDEX(`from`)," +
	        "   INDEX(`lives_in`)" +
            ") ENGINE=InnoDB;" +

            "CREATE TABLE Relatives(" +
	        "    `person_a` int(11) NOT NULL," +
	        "    `person_b` int(11) NOT NULL," +
	        "    `role_a` varchar(255) NOT NULL," +
	        "    `role_b` varchar(255) NOT NULL," +
	        "    `type` varchar(255) NOT NULL," +
	        "    `start_date` date," +
	        "    `end_date` date," +
	        "    PRIMARY KEY(`person_a`,`person_b`,`role_a`,`role_b`)," +
	        "    FOREIGN KEY(`person_a`) REFERENCES Person(`person_id`) ON UPDATE CASCADE ON DELETE CASCADE," +
	        "    FOREIGN KEY(`person_b`) REFERENCES Person(`person_id`) ON UPDATE CASCADE ON DELETE CASCADE" +
            ") ENGINE=InnoDB;" +

            "CREATE TABLE Traits(" +
	        "    `trait_name` varchar(255) NOT NULL," +
	        "    `category` varchar(255) NOT NULL," +
	        "    PRIMARY KEY(`trait_name`)" +
            ") ENGINE=InnoDB;" +

            "CREATE TABLE HasTraits(" +
	        "    `person` int(11) NOT NULL," +
	        "    `trait` varchar(255) NOT NULL," +
	        "    PRIMARY KEY(`person`,`trait`)," +
	        "    FOREIGN KEY(`person`) REFERENCES Person(`person_id`) ON UPDATE CASCADE ON DELETE CASCADE," +
	        "    FOREIGN KEY(`trait`) REFERENCES Traits(`trait_name`) ON UPDATE CASCADE ON DELETE CASCADE" +
            ") ENGINE=InnoDB;";
        }
    }
}
